package cn.kinggm520;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class PoiDemo {
    //当前文件已经存在
    private static String excelPath = "C:\\Users\\Administrator\\Desktop\\POIDemo\\template\\模板.xlsx";
    //导出文件
    private static String fileName = "C:\\Users\\Administrator\\Desktop\\POIDemo\\导出数据.xlsx";

    //从第几行插入进去
    private static int insertStartPointer = 2;

    //在当前工作薄的那个工作表单中插入这行数据
    private static String sheetName = "Sheet1";


    public static void main(String[] args) throws IOException {
        insertRows();
        System.out.println("导出成功");
    }



    /**
     * 在已有的Excel文件中插入一行新的数据的入口方法
     */
    public static void insertRows() {
        XSSFWorkbook wb = returnWorkBookGivenFileHandle();
        XSSFSheet sheet1 = wb.getSheet(sheetName);

        // 获取导入数据
        List<ExcelTestData> data = data();
        // 由于每插入一条数据 再插入第二条数据时 都会将第一条插入的数据下移 为了保证和导入的数据顺序一致 先将源数据倒叙排序
        Collections.reverse(data);

        BigDecimal countAge = new BigDecimal(0);
        BigDecimal countMoney = new BigDecimal(0);
        for (ExcelTestData v : data) {
            String age = v.getAge();
            String money = v.getMoney();
            if (StringUtils.isNotEmpty(age)) {
                BigDecimal ageBigDecimal = new BigDecimal(age);
                countAge = countAge.add(ageBigDecimal);
            }

            if (StringUtils.isNotEmpty(money)) {
                BigDecimal moneyBigDecimal = new BigDecimal(money);
                countMoney = countMoney.add(moneyBigDecimal);
            }
        }
        BigDecimal countAgeDeal = countAge.setScale(2, BigDecimal.ROUND_HALF_UP);
        BigDecimal countMoneyDeal = countMoney.setScale(2, BigDecimal.ROUND_HALF_UP);

        // 替换模板中的统计数据
        XSSFRow countRow = sheet1.getRow(insertStartPointer);
        // 单元格样式 设置水平垂直居中
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        // 遍历当前行的所有单元格
        for (int j = 0; j < countRow.getPhysicalNumberOfCells(); j++) {
            XSSFCell cell = countRow.getCell(j);
            String stringCellValue = cell.getStringCellValue();
            if ("${countAge}".equals(stringCellValue)) {
                String countAgeDealStr = String.valueOf(countAgeDeal);
                String[] split = countAgeDealStr.split("\\.");
                if ("00".equals(split[1])) {
                    countAgeDealStr = split[0];
                }
                cell.setCellValue(countAgeDealStr);
            }


            if ("${countMoney}".equals(stringCellValue)) {
                String countMoneyDealStr = String.valueOf(countMoneyDeal);
                String[] split = countMoneyDealStr.split("\\.");
                if ("00".equals(split[1])) {
                    countMoneyDealStr = split[0];
                }
                cell.setCellValue(countMoneyDealStr);
            }

            // 设置水平垂直居中
            cell.setCellStyle(cellStyle);
        }

        for (int i = 0; i < data.size(); i++) {
            XSSFRow row = null;
            if (sheet1.getRow(insertStartPointer) != null) {
                int lastRowNo = sheet1.getLastRowNum();
                sheet1.shiftRows(insertStartPointer, lastRowNo, 1);
            }

            // 创建行
            row = sheet1.createRow(insertStartPointer);

            // 创建单元格
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellStyle(cellStyle);
            cell0.setCellValue(data.get(i).getName());

            XSSFCell cell1 = row.createCell(1);
            cell1.setCellStyle(cellStyle);
            cell1.setCellValue(data.get(i).getAge());

            XSSFCell cell2 = row.createCell(2);
            cell2.setCellStyle(cellStyle);
            cell2.setCellValue(data.get(i).getMoney());

        }

        // 统一设置行高
        int physicalNumberOfRows = sheet1.getPhysicalNumberOfRows();
        for (int k = 0 ;k < physicalNumberOfRows; k++){
            // 表头不做处理
            if(k < insertStartPointer){
                continue;
            }
            XSSFRow row = sheet1.getRow(k);
            row.setHeight((short)(12*40));
        }

        saveExcel(wb);

    }


    /**
     * 得到一个已有的工作薄的POI对象
     *
     * @return
     */
    private static XSSFWorkbook returnWorkBookGivenFileHandle() {
        XSSFWorkbook wb = null;
        FileInputStream fis = null;
        File f = new File(excelPath);
        try {
            if (f != null) {
                fis = new FileInputStream(f);
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }


    /**
     * 保存工作薄
     *
     * @param wb
     */
    private static void saveExcel(XSSFWorkbook wb) {
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(fileName);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    public static List<ExcelTestData> data() {
        ExcelTestData excelTestData1 = new ExcelTestData("张三", "13", "100");
        ExcelTestData excelTestData2 = new ExcelTestData("李四", "14", "200");
        ExcelTestData excelTestDate3 = new ExcelTestData("王五", "15", "300");
        ExcelTestData excelTestDate4 = new ExcelTestData("赵六", "17", "400.123");
        ExcelTestData excelTestDate5 = new ExcelTestData("秀逗", "18", "666.66");

        ArrayList<ExcelTestData> excelTestData = new ArrayList<>();
        excelTestData.add(excelTestData1);
        excelTestData.add(excelTestData2);
        excelTestData.add(excelTestDate3);
        excelTestData.add(excelTestDate4);
        excelTestData.add(excelTestDate5);

        return excelTestData;
    }
}


class ExcelTestData {
    public ExcelTestData(String name, String age, String money) {
        this.name = name;
        this.age = age;
        this.money = money;
    }

    public ExcelTestData() {
    }

    private String name;

    private String age;

    private String money;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getMoney() {
        return money;
    }

    public void setMoney(String money) {
        this.money = money;
    }
}

